Imports System.Data.OleDb
Imports DTO

Public Class MonHocDAO
    Inherits DataProviderDAO

    'lay bang
    Public Function LayBang() As DataTable
        Dim dt As New DataTable
        Dim cn As OleDbConnection
        'B1 & B2: Tao chuoi ket noi, mo ket noi bang doi tuong ket noi
        cn = Me.ConnectionData()
        'B3: Tao chuoi strSQL thao tac CSDL
        Dim strSQL As String
        strSQL = "Select MaMH, TenMH From MonHoc"
        'B4: Thuc thi chuoi strSQL
        Dim da As OleDbDataAdapter
        da = New OleDbDataAdapter(strSQL, cn)
        da.Fill(dt)
        'B5: Dong ket noi CSDL
        cn.Close()
        Return dt
    End Function

    'lay danh sach gv phu trach lop hoc theo mon hoc
    Public Function GVPT(ByVal maMH As Integer) As DataTable
        Dim dt As New DataTable
        Dim cn As OleDbConnection
        cn = Me.ConnectionData()
        Dim strSQL As String
        strSQL = "SELECT GV.HoTen, LH.TenLH " + _
                 "FROM GiaoVien GV, PhanCong PC, LopHoc LH, MonHoc MH " + _
                 "WHERE GV.MaGV = PC.MaGV and PC.MaLH = LH.MaLH and GV.MaMH = MH.MaMH and MH.MaMH = " + maMH.ToString()

        Dim da As OleDbDataAdapter
        da = New OleDbDataAdapter(strSQL, cn)
        da.Fill(dt)
        cn.Close()
        Return dt
    End Function

    'them mon hoc
    Public Sub Them(ByVal mh As MonHocDTO)
        Dim cn As New OleDbConnection
        cn = Me.ConnectionData

        Dim strSQL As String
        strSQL = "Insert into MonHoc (TenMH) values('" + mh.TenMH + "')"

        Dim cm As New OleDbCommand(strSQL, cn)
        cm.ExecuteNonQuery()

        strSQL = "Select @@IDENTITY"
        cm = New OleDbCommand(strSQL, cn)

        mh.MaMH = cm.ExecuteScalar()

        cn.Close()
    End Sub


    Public Sub SuaMonHoc(ByVal mh As MonHocDTO)
        Dim cn As New OleDbConnection
        cn = Me.ConnectionData

        Dim strSQL As String
        strSQL = "Update MonHoc set TenMH  = '" & mh.TenMH & "' where MaMH= " & mh.MaMH

        Dim cm As New OleDbCommand(strSQL, cn)
        cm.ExecuteNonQuery()

        strSQL = "select @@IDENTITY"
        cm = New OleDbCommand(strSQL, cn)

        mh.MaMH = cm.ExecuteScalar()

        cn.Close()



    End Sub

End Class
